package ATM;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UtilSql1 {


    // 创建连接方法
    public Connection getConnection() {
        Connection con = null;
        try {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/atm?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root", "19970328");
//            System.out.println("con:" + con);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        return con;
    }

    public void sub(double moneyOrigin, double money,String id) {
        //1.创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //2. 获取执行SQL语句对象
            statement = con.createStatement();
            String sql = "update t_user set u_count ="+(moneyOrigin - money)+" where u_id ='"+id+"'";
            //3.执行SQL语句
            int row = statement.executeUpdate(sql);//被影响了几行
            System.out.println("影响的行数" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //4.关闭连接
            close(statement, con);
        }

    }

    public void psssWordModify(String password,String id) {
        //1.创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //2. 获取执行SQL语句对象
            statement = con.createStatement();
            String sql = "update t_user set u_pass_word ="+password+" where u_id ='"+id+"'";
            //3.执行SQL语句
           statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //4.关闭连接
            close(statement, con);
        }

    }
    public void transfer(String id ,double moneyOrigin, String id1,double moneyOrigin1,double money ){
        //1.创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //2. 获取执行SQL语句对象
            statement = con.createStatement();
            String sql = "update t_user set u_count ="+(moneyOrigin - money)+" where u_id ='"+id+"'";
            String sql1 = "update t_user set u_count ="+(moneyOrigin1 + money)+" where u_id ='"+id1+"'";
            statement.executeUpdate(sql);
            statement.executeUpdate(sql1);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //4.关闭连接
            close(statement, con);
        }

    }






    public void add(double moneyOrigin, double money,String id) {
        //1.创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //2. 获取执行SQL语句对象
            statement = con.createStatement();
            String sql = "update t_user set u_count ="+(moneyOrigin + money)+" where u_id ='"+id+"'";
            statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //4.关闭连接
            close(statement, con);
        }

    }

    /**
     * 增加数据
     */
    public void insert(String name, String gender,int score) {
        //1.创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //2. 获取执行SQL语句对象
            statement = con.createStatement();
            String sql = "insert into t_student(s_name,s_gender,s_score) values('" + name + "','" + gender + "'," + score + ")";
            //3.执行SQL语句
            int row = statement.executeUpdate(sql);//被影响了几行
            System.out.println("影响的行数" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //4.关闭连接
            close(statement, con);
        }

    }

    //查询
    public List query() {
        //创建连接
        Connection con = getConnection();
        Statement statement = null;
        ResultSet rs = null;
        List list = new ArrayList();

        //2.获取换行SQL语句的对象
        try {
            statement = con.createStatement();
            String sql = "select u_id id, u_pass_word passWord, u_count count from t_user";
            //3.执行SQL并返回结果集
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Users users = new Users();
                users.setId(rs.getString("id"));
                users.setPassWord(rs.getString("passWord"));
                users.setCount(rs.getDouble("count"));
                list.add(users);
//                atmJframe.getUsersArrayList().add(users);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(statement, con, rs);
        }
        return  list;
//        return atmJframe.getUsersArrayList();
    }

    public void close(Statement statement, Connection con) {
        //4.关闭连接

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public void close(Statement statement, Connection con, ResultSet resultSet) {
        //4.关闭连接
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        UtilSql1 test01 = new UtilSql1();
        List list = test01.query();

        System.out.println(list);


//        test01.getConnection();
//        test01.insert("小晓", "女", 88);
//                List stuList = test01.query();
//        System.out.print(stuList);
    }
}
